package main

import (
	_ "github.com/Go-SQL-Driver/MySQL"
	"github.com/go-xorm/xorm"
	"fmt"
	"github.com/go-xorm/core"
	"xorm_demo/model"
)

var engine *xorm.Engine

func main() {

	//创建表结构和索引
	//craateTableAndIndex()

	//插入数据
	//insert()

	//删除数据
	//delete()

	//修改数据
	//update()

	//查询数据
	//query()

	//sql原生查询
	//sqlQuery()

	//sql原生失误操作
	//sqlExec()
}

func init() {
	var err error
	engine, err = xorm.NewEngine("mysql", "root:123456@tcp(127.0.0.1:3306)/xorm?charset=utf8&parseTime=True&loc=Local")
	if err != nil {
		fmt.Println(err)
	}
	//设置是否显示sql
	engine.ShowSQL(true)
	//设置日志级别
	engine.Logger().SetLevel(core.LOG_DEBUG)
	//设置映射表名称前缀
	mapper := core.NewPrefixMapper(core.SnakeMapper{}, "xorm_")
	//设置表名映射mapper
	engine.SetTableMapper(mapper)
	// 设置字段名映射mapper
	engine.SetColumnMapper(core.SnakeMapper{})
}

//创建表结构，如果表结构存在，则删除重新创建
func craateTableAndIndex() {
	//判断表是否已经存在
	if exist, _ := engine.IsTableExist(model.User{}); exist {
		// 删除表结构
		engine.DropTables(model.User{})
	}
	//创建表结构
	engine.CreateTables(&model.User{})
	//创建表索引
	engine.CreateIndexes(&model.User{})
}

func insert() {
	//判断表中数据是否为空
	user := model.User{}
	user.UserName = "xorm"
	user.Age = 10
	count, err := engine.Insert(&user)
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Printf("effect count %d \n", count)
	fmt.Println(user)
}

//删除数据
func delete() {
	count, err := engine.ID(2).Delete(model.User{})
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Printf("soft delete effect count %d\n", count)

	count, err = engine.ID(3).Unscoped().Delete(model.User{})
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Printf("hard delete effect count %d\n", count)

}

func update() {
	user := model.User{}
	user.UserName = "xorm_test"
	count, err := engine.ID(1).Update(&user)
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Printf("update effect count %d \n", count)
	fmt.Println(user)

	count, err = engine.ID(2).Cols("user_name", "age").Update(user)
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Printf("update effect count %d \n", count)
	fmt.Println(user)
}

func query() {
	user := model.User{}
	engine.Alias("t").Where("t.user_name=?", "xorm_test1").And("t.age=?", 10).Get(&user)
	fmt.Println(user)

	user1 := model.User{}
	user1.UserName = "xorm_test"
	hasRecord, error := engine.Get(&user1)
	if error != nil {
		fmt.Println(error.Error())
	}
	if hasRecord {
		fmt.Printf("record exist and the value returned %v\n", user1)
	}

	user2 := model.User{}
	user2.UserName = "xorm_test"
	exist, error := engine.Exist(&user2)
	if error != nil {
		fmt.Println(error.Error())
	}
	fmt.Printf("record %v\n", exist)
	if exist {
		fmt.Printf("record exist but there is no value returned %v\n", user2)
	}

	users := make([]model.User, 0)

	engine.Find(&users)

	for i, v := range users {
		fmt.Printf("number %d,value is %v\n", i, v)
	}
}

//原生sql查询
func sqlQuery() {
	sql := "select * from xorm_user where user_name=? and age =?"
	users, error := engine.Query(sql, "xorm_test", 2)
	if error != nil {
		fmt.Printf(error.Error())
	}

	for index, tempUserMap := range users {
		fmt.Printf("number %d the value is %v", index, tempUserMap)
		for key, value := range tempUserMap {
			fmt.Printf("%v : %v\n", key, value)
		}
	}

}

//原生sql执行事务操作
func sqlExec() {
	sql := "update xorm_user set age=101 where id=?"
	result, error := engine.Exec(sql, 5)
	if error != nil {
		fmt.Println(error.Error())
	}
	count, _ := result.RowsAffected()
	lastId, _ := result.LastInsertId()
	fmt.Printf("the count is %d ,the lastId is %d\n", count, lastId)

}
